package com.bckj.fastboot.core.sql;

import com.bckj.fastboot.core.lang.exception.BusinessException;
import lombok.Getter;
import org.springframework.util.Assert;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.function.Consumer;

/**
 * SqlBuilder
 *
 * @author wfc
 **/
@Getter
public class Sql {

    private final List<String> select;
    private final List<String> froms;
    private final List<String> joins;
    private final List<String> leftJoins;
    private final List<String> rightJoins;
    private final List<String> groups;
    private final List<String> orders;
    private final List<String> columns;
    private final List<String> values;
    private final List<String> sets;
    private final WhereNode wheres;

    private String lastSql;
    private int type;

    public Sql(
            List<String> select,
            List<String> froms,
            List<String> joins,
            List<String> leftJoins,
            List<String> rightJoins,
            List<String> groups,
            List<String> orders,
            List<String> columns,
            List<String> values,
            List<String> sets,
            WhereNode wheres
    ) {
        this.select = select;
        this.froms = froms;
        this.joins = joins;
        this.leftJoins = leftJoins;
        this.rightJoins = rightJoins;
        this.groups = groups;
        this.orders = orders;
        this.columns = columns;
        this.values = values;
        this.sets = sets;
        this.wheres = wheres;
    }

    public static Sql of() {
        return new Sql(
                List.of(),
                List.of(),
                List.of(),
                List.of(),
                List.of(),
                List.of(),
                List.of(),
                null,
                null,
                null,
                WhereNode.of()
        );
    }

    public static Sql of(
            List<String> select,
            List<String> froms,
            List<String> joins,
            List<String> leftJoins,
            List<String> rightJoins,
            List<String> groups,
            List<String> orders,
            WhereNode wheres
    ) {
        return new Sql(
                select,
                froms,
                joins,
                leftJoins,
                rightJoins,
                groups,
                orders,
                null,
                null,
                null,
                wheres
        );
    }

    public Sql select(String... select) {
        this.select.addAll(Arrays.asList(select));
        return this;
    }

    public Sql from(String... tables) {
        this.froms.add(String.join(",", Arrays.asList(tables)));
        return this;
    }

    public Sql join(String... joins) {
        this.joins.addAll(Arrays.asList(joins));
        return this;
    }

    public Sql leftJoin(String... joins) {
        this.leftJoins.addAll(Arrays.asList(joins));
        return this;
    }

    public Sql rightJoin(String... joins) {
        this.rightJoins.addAll(Arrays.asList(joins));
        return this;
    }

    public Sql and(String where) {
        wheres.and(where);
        return this;
    }

    public Sql and(WhereNode where) {
        wheres.group(where);
        return this;
    }

    public Sql or(String where) {
        wheres.or(where);
        return this;
    }

    public Sql and(Consumer<WhereNode> consumer) {
        WhereNode whereNode = WhereNode.of();
        consumer.accept(whereNode);
        wheres.group(whereNode);
        return this;
    }

    public Sql groupBy(String... groups) {
        this.groups.addAll(Arrays.asList(groups));
        return this;
    }

    public Sql orderBy(String... orders) {
        this.orders.addAll(Arrays.asList(orders));
        return this;
    }

    public Sql lastSql(String lastSql) {
        this.lastSql = lastSql;
        return this;
    }

    public Sql insert(String table) {
        this.type = 1;
        this.from(table);
        return this;
    }

    public Sql values(String columns, String values) {
        this.columns.add(columns);
        this.values.add(values);
        return this;
    }

    public Sql update(String table) {
        this.type = 3;
        this.from(table);
        return this;
    }

    public Sql set(String sets, String values) {
        this.sets.add(sets);
        this.values.add(values);
        return this;
    }

    public Sql delete(String table) {
        this.type = 2;
        this.from(table);
        return this;
    }

    public String toString() {
        return switch (type) {
            case 1 ->
                 buildInsert();
            case 2 ->
                 buildDelete();
            case 3 ->
                 buildUpdate();
            default ->
                 buildSelect();
        };
    }

    private String buildSelect() {
        Assert.notEmpty(select, "select is empty");
        Assert.notEmpty(froms, "froms is empty");
        StringBuilder sql = new StringBuilder("SELECT ");
        sql.append(String.join(",", select));
        sql.append("\nFROM ");
        sql.append(String.join(",", froms));
        if (!joins.isEmpty()) {
            joins.forEach(p -> {
                sql.append("\n\tJOIN ").append(p);
            });
        }
        if (!leftJoins.isEmpty()) {
            leftJoins.forEach(p -> {
                sql.append("\n\tLEFT JOIN ").append(p);
            });
        }
        if (!rightJoins.isEmpty()) {
            rightJoins.forEach(p -> {
                sql.append("\n\tRIGHT JOIN ").append(p);
            });
        }
        if (!wheres.children.isEmpty()) {
            sql.append("\nWHERE ").append(wheres);
        }
        if (!groups.isEmpty()) {
            sql.append("\nGROUP BY ").append(String.join(",", groups));
        }
        if (!orders.isEmpty()) {
            sql.append("\nORDER BY ").append(String.join(",", orders));
        }
        if (lastSql != null) {
            sql.append("\n").append(lastSql);
        }
        return sql.toString();
    }

    private String buildInsert() {
        Assert.notEmpty(columns, "columns is empty");
        Assert.notEmpty(values, "values is empty");
        if (this.froms.size() != 1) {
            throw new BusinessException("froms size is not 1");
        }
        StringBuilder sql = new StringBuilder("INSERT INTO ");
        sql.append(froms.get(0));
        sql.append("(");
        sql.append(String.join(",", columns));
        sql.append(") VALUES (");
        sql.append(String.join(",", values));
        sql.append(")");
        return sql.toString();
    }

    private String buildUpdate() {
        Assert.notEmpty(sets, "sets is empty");
        Assert.notEmpty(values, "values is empty");
        if (this.froms.size() != 1) {
            throw new BusinessException("froms size is not 1");
        }
        StringBuilder sql = new StringBuilder("UPDATE ");
        sql.append(froms.get(0));
        sql.append(" SET ");
        sql.append(String.join(",", sets));
        sql.append(" WHERE ");
        sql.append(values.get(0));
        return sql.toString();
    }

    private String buildDelete() {
        if (this.froms.size() != 1) {
            throw new BusinessException("froms size is not 1");
        }
        StringBuilder sql = new StringBuilder("DELETE FROM ");
        sql.append(froms.get(0));
        if (!wheres.children.isEmpty()) {
            sql.append("\nWHERE ").append(wheres);
        }
        return sql.toString();
    }

    public record WhereNode(String where, List<WhereNode> children) {

        public static WhereNode of(String where) {
            return new WhereNode(where, null);
        }

        public static WhereNode of() {
            return new WhereNode(null, new ArrayList<>());
        }

        public WhereNode group(WhereNode where) {
            if (this.where != null) {
                throw new BusinessException("group whereStr is not empty");
            }
            if (where.children == null) {
                throw new BusinessException("whereNode children is empty");
            }
            children.add(where);
            return this;
        }

        public WhereNode and(String where) {
            if (children.isEmpty()) {
                children.add(WhereNode.of(where));
            } else {
                children.add(WhereNode.of(" AND " + where));
            }
            return this;
        }

        public WhereNode or(String where) {
            if (children.isEmpty()) {
                throw new BusinessException("whereNode is empty");
            } else {
                children.add(WhereNode.of(" OR " + where));
            }
            return this;
        }

        boolean isGroup() {
            return children != null;
        }

        @Override
        public String toString() {
            if (where != null) {
                return where;
            }
            if (children.isEmpty()) {
                return "";
            }
            StringBuilder sql = new StringBuilder("(");
            for (int i = 0; i < children.size(); i++) {
                WhereNode whereNode = children.get(i);
                if (i > 0) {
                    if (whereNode.isGroup()) {
                        sql.append(" AND ");
                    }
                }
                sql.append(whereNode);
            }
            sql.append(")");
            return sql.toString();
        }

    }

}
